import mysql.connector

# 连接到远程MySQL数据库
cnx = mysql.connector.connect(
    host='10.30.93.169',
    port=3386,
    user='developer',
    password='wFQ&E7%2iIf$NTTC',
    database='recruitment'
)

file = open('/Users/lch/data/findMysqlTable2.txt', 'w')

# 获取数据库游标
cursor = cnx.cursor()

# 获取所有表名
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()
noNeedTables = ["wash_similar_person", "wash_resume_label", "wash_school_history",
                "wash_repeat_person", "v_resume", "resume_props_transition", "resume_major_extend",
                "resume_lock_setting", "resume_export_task_operate_record", "recruitment_tag",
                "recruitment_school", "recruitment_notify_white_list", "qbi_file_20220909_20_30_29",
                "async_process_data", "email_link_account", "head_hunter_contact",
                "A"]

# 遍历所有表
for table in tables:
    table_name = table[0]
    if table_name in noNeedTables:
        continue

    # 查询表结构
    cursor.execute(f"DESCRIBE {table_name}")
    columns = cursor.fetchall()

    # 检查是否包含company_id字段
    has_company_id = False
    for column in columns:
        if column[0] == 'company_id':
            has_company_id = True
            break

    # 如果不包含company_id字段或者company_id=0，则打印表名
    if not has_company_id:
        print(table_name)
        file.write(table_name + "\n")
        # cursor.execute(f"SELECT count(*) as coun FROM {table_name}")
        # result = cursor.fetchone()
        # for res in result:
        #     if res < 1000:
        #         cursor.execute(f"SELECT * FROM {table_name}")
        #         columns = [column[0] for column in cursor.description]
        #         insert_script = ''
        #         for row in cursor.fetchall():
        #             values = ', '.join([f"'{value}'" if isinstance(value, str) else str(value) for value in row])
        #             insert_script += f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({values});\n"
        #             # 打印insert脚本
        #             print(insert_script)
        #             file.write(insert_script)
    else:
        cursor.execute(f"SELECT * FROM {table_name} WHERE company_id='0' limit 0,1")
        result = cursor.fetchone()
        # 输出表名和符合条件的记录
        if result is not None:
            print(table_name)
            file.write(table_name + "\n")
            # cursor.execute(f"SELECT * FROM {table_name} WHERE company_id='0'")
            # columns = [column[0] for column in cursor.description]
            # insert_script = ''
            # for row in cursor.fetchall():
            #     values = ', '.join([f"'{value}'" if isinstance(value, str) else str(value) for value in row])
            #     insert_script += f"INSERT INTO {table_name} ({', '.join(columns)}) VALUES ({values});\n"
            #     # 打印insert脚本
            #     print(insert_script)
            #     file.write(insert_script)

    # 关闭数据库连接
cursor.close()
cnx.close()
file.close()
